﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;

namespace Songhay.DataAccess
{
    /// <summary>
    /// Generic procedures for data access.
    /// </summary>
    static public class CommonDbms
    {
        /// <summary>
        /// Closes a database connection.
        /// </summary>
        /// <param name="connection">
        /// A <see cref="System.Data.Common.DbConnection"/>.
        /// </param>
        public static void Close(DbConnection cnn)
        {
            CommonDbms.Close(cnn);
        }

        /// <summary>
        /// Closes a database connection.
        /// </summary>
        /// <param name="connection">
        /// An instance implementing <see cref="System.Data.IDbConnection"/>.
        /// </param>
        public static void Close(IDbConnection cnn)
        {
            if (cnn == null) return;

            if (cnn.State == ConnectionState.Closed)
            {
                cnn.Dispose();
                return;
            }

            if (cnn.State != ConnectionState.Closed)
            {
                try
                {
                    cnn.Close();
                }
                finally
                {
                    cnn.Dispose();
                }
            }
        }

        /// <summary>
        /// Executes a SQL sqlStatement for the current <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="connection">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(DbConnection cnn, String sqlStatement)
        {
            return DoCommand(cnn, null, sqlStatement, null);
        }

        /// <summary>
        /// Executes a SQL Statement for the current instance of <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="connection">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(IDbConnection cnn, String sqlStatement)
        {
            return DoCommand(cnn, null, sqlStatement, null);
        }

        /// <summary>
        /// Executes a SQL Statement for the current <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="connection">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="ambientTransaction">The explicit, server <see cref="System.Data.Common.DbTransaction"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(DbConnection cnn, DbTransaction ambientTransaction, String sqlStatement)
        {
            return DoCommand(cnn, ambientTransaction, sqlStatement, null);
        }

        /// <summary>
        /// Executes a SQL sqlStatement for the current instance of <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="connection">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="ambientTransaction">An object implementing the explicit, server <see cref="System.Data.IDbTransaction"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(IDbConnection cnn, IDbTransaction ambientTransaction, String sqlStatement)
        {
            return DoCommand(cnn, ambientTransaction, sqlStatement, null);
        }

        /// <summary>
        /// Executes a SQL sqlStatement for the current <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="connection">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <param name="parameterCollection">The parameters.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(DbConnection cnn, String sqlStatement, Dictionary<String, Object> parameterCollection)
        {
            return DoCommand(cnn, null, sqlStatement, parameterCollection);
        }

        /// <summary>
        /// Executes a SQL sqlStatement for the current instance of <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="connection">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <param name="parameterCollection">The parameters.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(IDbConnection cnn, String sqlStatement, Dictionary<String, Object> parameterCollection)
        {
            return DoCommand(cnn, null, sqlStatement, parameterCollection);
        }

        /// <summary>
        /// Executes a SQL sqlStatement for the current <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="connection">The <see cref="System.Data.Common.DbConnection"/>.</param>
        /// <param name="ambientTransaction">The explicit, server <see cref="System.Data.Common.DbTransaction"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <param name="parameterCollection">The parameters.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(DbConnection cnn, DbTransaction ambientTransaction, String sqlStatement, Dictionary<String, Object> parameterCollection)
        {
            return DoCommand(cnn as IDbConnection, ambientTransaction, sqlStatement, parameterCollection);
        }

        /// <summary>
        /// Executes a SQL sqlStatement for the current instance of <see cref="System.Data.IDbConnection"/>.
        /// </summary>
        /// <param name="connection">The object implementing <see cref="System.Data.IDbConnection"/>.</param>
        /// <param name="ambientTransaction">An instance of the explicit, server <see cref="System.Data.IDbTransaction"/>.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <param name="parameterCollection">The parameters.</param>
        /// <returns>Returns the number of records affected.</returns>
        public static int DoCommand(IDbConnection cnn, IDbTransaction ambientTransaction, String sqlStatement, Dictionary<String, Object> parameterCollection)
        {
            if (cnn == null) throw new ArgumentNullException("connection", "The implementing Connection object is null.");
            if (String.IsNullOrEmpty(sqlStatement)) throw new ArgumentException("The DBMS SQL Statement was not specified.");

            int i = 0;

            using (IDbCommand cmd = cnn.CreateCommand())
            {
                if (ambientTransaction != null) cmd.Transaction = ambientTransaction;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlStatement;

                if (parameterCollection != null)
                {
                    IDataParameter[] paramArray = CommonParameter.GetParameters(cmd, parameterCollection);
                    foreach (IDataParameter p in paramArray)
                    {
                        cmd.Parameters.Add(p);
                    }
                }

                i = cmd.ExecuteNonQuery();

            }

            return i;
        }

        /// <summary>
        /// Returns a <see cref="System.Data.Common.DbDataAdapter"/>.
        /// </summary>
        /// <param name="invariantProviderName">
        /// The invariant name of the data provider.
        /// </param>
        public static DbDataAdapter GetAdapter(String invariantProviderName)
        {
            if (String.IsNullOrEmpty(invariantProviderName)) throw new ArgumentException("The Invariant Provider Name was not specified.");

            DbProviderFactory factory = DbProviderFactories.GetFactory(invariantProviderName);
            return factory.CreateDataAdapter();
        }

        /// <summary>
        /// Returns a <see cref="System.Data.Common.DbDataAdapter"/>.
        /// </summary>
        /// <param name="invariantProviderName">
        /// The invariant name of the data provider.
        /// </param>
        /// <param name="connectionConfiguration">
        /// The provider connection string.
        /// </param>
        /// <param name="query">
        /// The SELECT statement used to generate SELECT, INSERT, UPDATE, DELETE
        /// <see cref="System.Data.Common.DbCommand"/> commands.
        /// </param>
        public static DbDataAdapter GetAdapter(String invariantProviderName, String connectionConfiguration, String query)
        {
            if (String.IsNullOrEmpty(invariantProviderName)) throw new ArgumentException("The Invariant Provider Name was not specified.");
            if (String.IsNullOrEmpty(connectionConfiguration)) throw new ArgumentException("The DBMS Connection String was not specified.");
            if (String.IsNullOrEmpty(query)) throw new ArgumentException("The DBMS query was not specified.");

            DbProviderFactory factory = DbProviderFactories.GetFactory(invariantProviderName);
            DbDataAdapter adapter = factory.CreateDataAdapter();

            if (!String.IsNullOrEmpty(connectionConfiguration) && !String.IsNullOrEmpty(query))
            {
                DbConnection cnn = factory.CreateConnection();
                cnn.ConnectionString = connectionConfiguration;

                DbCommand selectCommand = factory.CreateCommand();
                selectCommand.CommandText = query;
                selectCommand.Connection = cnn;

                DbCommandBuilder builder = factory.CreateCommandBuilder();
                builder.DataAdapter = adapter;
                adapter.SelectCommand = selectCommand;

                adapter.DeleteCommand = builder.GetDeleteCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.UpdateCommand = builder.GetUpdateCommand();
            }

            return adapter;
        }

        /// <summary>
        /// Returns a <see cref="System.Data.Common.DbConnection"/>.
        /// </summary>
        /// <param name="invariantProviderName">
        /// The invariant name of the data provider.
        /// </param>
        /// <param name="connectionConfiguration">
        /// The provider connection string.
        /// </param>
        public static DbConnection GetConnection(String invariantProviderName, String connectionConfiguration)
        {
            if (String.IsNullOrEmpty(invariantProviderName)) throw new ArgumentException("The Invariant Provider Name was not specified.");
            if (String.IsNullOrEmpty(connectionConfiguration)) throw new ArgumentException("The DBMS Connection String was not specified.");

            DbProviderFactory factory = DbProviderFactories.GetFactory(invariantProviderName);
            DbConnection cnn = factory.CreateConnection();
            cnn.ConnectionString = connectionConfiguration;
            return cnn;
        }
    }
}
